package mt;

import java.security.MessageDigest;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import front.*;
import be.InsertList;
import be.backend;

public class middle {
	
	public backend be;
	
	public middle(backend be){
		this.be = be;//this is the be. BEBEBEBE!
	}
	
	public boolean login(String username, String password){
		try {
			
			//HASHING!
			byte[] data = password.getBytes();
			MessageDigest md = MessageDigest.getInstance("SHA-256");
			md.update(data);
			byte[] hash = md.digest();
			String hashedPassword = new String(hash);
			hashedPassword = hashedPassword.replace('\'', 'f');
			hashedPassword = hashedPassword.replace('\"', 'k');
			
			InsertList insert = new InsertList();
			insert.addVarchar("email", username);
			insert.addVarchar("password", hashedPassword);

			ResultSet rs = be.getData("user", new InsertList("userID"), insert, "LIMIT 1");
			if (rs == null) {
				System.out.println("LOGIN FAIL! FOUND NO SUCH USER//PASSWORD");
			}
			while (rs.next()) {
				if (rs.wasNull()) {
					System.out.println("LOGIN FAIL! FOUND NO SUCH USER//PASSWORD");
					return false;
				} else {
					//Good login!
					System.out.println("Good login!");
					return true;
				}
			}
		} catch (Exception e) {
			System.out.println("login error!");
			e.printStackTrace();
			return false;
		}
		return false;
	}
	
	public int CreatePM(int pmId, int from, int to, Timestamp date, String message){
		InsertList insert = new InsertList();
			
		insert.addInt("toUserID", ""+to);
		insert.addInt("fromUserID", ""+from);
		insert.addDate("date", date.toString());
		insert.addVarchar("message", message);
		
		return be.Insert("pm", insert);
	}
	
	public int CreatePost(int postID, int threadID, int userID, String content, Timestamp createdOn, Timestamp lasteditedOn){
		InsertList insert = new InsertList();
			
		insert.addInt("postID", ""+postID);
		insert.addInt("threadID", ""+threadID);
		insert.addInt("userID", ""+userID);
		insert.addVarchar("content", content);
		insert.addDate("createdOn", createdOn.toString());
		insert.addDate("lasteditedOn", lasteditedOn.toString());
		
		return be.Insert("post", insert);
	}
	
	public int CreateSubForum(int subForumID, String name, int numberOfThreads, Timestamp createdOn, int minimumRestrictionlvl){
		
		InsertList insert = new InsertList();
			
		insert.addInt("subForumID", ""+subForumID);
		insert.addInt("numberOfThreads", ""+numberOfThreads);
		insert.addInt("minimumRestrictionlvl", ""+minimumRestrictionlvl);
		insert.addVarchar("name", name);
		insert.addDate("createdOn", createdOn.toString());
		
		return be.Insert("subforum", insert);

	}
	
	public int CreateThread(int threadID, int subForumID,String name,int createdBy, Timestamp createdOn, int views, int replies){
		
		InsertList insert = new InsertList();
		insert.addInt("threadID", ""+threadID);
		insert.addInt("subForumID", ""+subForumID);
		insert.addInt("createdBy", ""+createdBy);
		insert.addInt("views", ""+views);
		insert.addInt("replies", ""+replies);
		insert.addVarchar("name", name);
		insert.addDate("createdOn", createdOn.toString());
		
		return be.Insert("thread", insert);
	}
	
	public int CreateUser(int userID, int userlvl, String name, String email, String password, Timestamp joined, int numberOfPosts){
		
		String hashedPassword= "";
		try {
			byte[] data = password.getBytes();
			MessageDigest md = MessageDigest.getInstance("SHA-256");
			md.update(data);
			byte[] hash = md.digest();
			
			hashedPassword = new String(hash).replace('\'', 'f').replace('\"', 'k');
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		if(!hashedPassword.equals("")){
			
			InsertList insert = new InsertList();
			insert.addInt("userID", ""+userID);
			insert.addInt("userlvl", ""+userlvl);
			insert.addInt("numberOfPosts", ""+numberOfPosts);
			insert.addVarchar("name", name);
			insert.addVarchar("email", email);
			insert.addDate("joined", joined.toString().split("\\.")[0]);
			
			insert.addVarchar("password", hashedPassword);
			
			return be.Insert("user", insert);
		}
		else{
			System.out.println("ERROR: HashedPassword=\"\"");
			return -1;
		}
	}
	
	public void DeletePM(int id){
		be.deleteData("pm",new InsertList("pmID", "varchar", ""+id));
	}
	public void DeletePost(int id){
		be.deleteData("post",new InsertList("postID", "varchar", ""+id));
	}
	public void DeleteSubForum(int id){
		be.deleteData("subforum",new InsertList("subForumID", "varchar", ""+id));
	}
	public void DeleteThread(int id){
		be.deleteData("thread",new InsertList("threadID", "varchar", ""+id));
	}
	
	public void DeleteUser(int id){
		be.deleteData("user",new InsertList("userID", "varchar", ""+id));
	}
	
	public ArrayList<front.User> getAllUsers(){
		try {
			
			ResultSet rs =  be.getData("user",null,null, "");
			
			ArrayList<front.User> result = new ArrayList<front.User>();
			
			front.User row;
			while(rs.next()){
				row  = new front.User(
						(Integer)rs.getObject("userID"), 
						(Integer)rs.getObject("userlvl"), 
						(String)rs.getObject("name"), 
						(String)rs.getObject("email"),
						(String)rs.getObject("password"),
						(Timestamp)rs.getObject("joined"),
						(Integer)rs.getObject("numberOfPosts"));
				result.add(row);
			}
			be.disconnect();
			return result;
		} catch (SQLException e) {
			System.out.println("SQL error!");
			e.printStackTrace();
		}catch (Exception e) {
			e.printStackTrace();
		}
		be.disconnect();
		return null;	
	}
	
	public ArrayList<front.SubForum> getAllSubForums(){
		try {
			
			ResultSet rs =  be.getData("subforum",null,null, "");
			
			ArrayList<front.SubForum> result = new ArrayList<front.SubForum>();
			
			front.SubForum row;
			while(rs.next()){
				row  = new SubForum(
						(Integer)rs.getObject("subForumID"), 
						(String)rs.getObject("name"), 
						(Integer)rs.getObject("numberOfThreads"), 
						(Timestamp)rs.getObject("createdOn"),
						(Integer)rs.getObject("minimumRestrictionlvl"));
				result.add(row);
			}
			be.disconnect();
			return result;
		} catch (SQLException e) {
			System.out.println("SQL error!");
			e.printStackTrace();
		}catch (Exception e) {
			e.printStackTrace();
		}
		be.disconnect();
		return null;	
	}
	
	public String getEmail(int userID){
		try {
			InsertList insert = new InsertList();
			insert.addInt("userID", ""+userID);
			
			ResultSet rs = be.getData("user", new InsertList("email"), insert,"LIMIT 1");
			
			if(rs == null)
				return "";
			if ( rs.next() ){
				String email = rs.getString(1);
				be.disconnect();
			    return email;
			}
			
		} catch (SQLException e) {
			System.out.println("PMgetEmail! catch!");
			e.printStackTrace();
		}
		return "";
	}
	
	public void setPostContent(int postID, String content){
		InsertList insert = new InsertList();
		insert.addVarchar("content", content);
		be.UpdateData("post", insert, new InsertList("postID","varchar",""+postID));
	}
	public void editPostContent(int postId,String newContent){
		setPostContent(postId, newContent);
	}
	
	public User getUser(int userID){
		try {
			
			InsertList where = new InsertList();
			where.addInt("userID", ""+userID);
			ResultSet rs = be.getData("user", null,where,"LIMIT 1");
			
			User result = null;
			while(rs.next()){
				result  = new User(
					rs.getInt("userID"),
					rs.getInt("userlvl"),
					rs.getString("name"),
					rs.getString("email"),
					rs.getString("password"),
					rs.getTimestamp("joined"),
					rs.getInt("numberOfPosts")
					);
			}
			be.disconnect();
			return result;
		
		} catch (SQLException e) {
			System.out.println("PMgetEmail! catch!");
			e.printStackTrace();
		}
		be.disconnect();
		return null;
	}


	
	public ArrayList<front.Thread> getAllThreads(int subForumID){
		try {
			InsertList where = new InsertList();
			where.addInt("subForumID", ""+subForumID);
			ResultSet rs =  be.getData("thread", null, where,"ORDER BY createdOn DESC");
			
			ArrayList<front.Thread> result = new ArrayList<front.Thread>();
			
			front.Thread row;
			while(rs.next()){
				row  = new front.Thread(
					(Integer)rs.getObject("threadID"), 
					(Integer)rs.getObject("subForumID"), 
					(String)rs.getObject("name"), 
					(Integer)rs.getObject("createdBy"), 
					(Timestamp)rs.getObject("createdOn"), 
					(Integer)rs.getObject("views"), 
					(Integer)rs.getObject("replies"));
				result.add(row);
			}
			be.disconnect();
			return result;
		} catch (SQLException e) {
			System.out.println("SQL error!");
			e.printStackTrace();
		}catch (Exception e) {
			e.printStackTrace();
		}
		be.disconnect();
		return null;
	}
	
	public String getSubForumName(int subForumID){
		try {
			InsertList where = new InsertList();
			where.addInt("subForumID", ""+subForumID);
			ResultSet rs = be.getData("subforum", new InsertList("name"), where,"LIMIT 1");
			
			rs.next();
			String name= rs.getString("name");
			be.disconnect();
			return name;
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return "";
	}
	
	public front.Thread subForumGetLatestThread(int subForumID){
		try {
			InsertList where = new InsertList();
			where.addInt("subForumID", ""+subForumID);
			ResultSet rs = be.getData("thread", null, where, "ORDER BY createdOn DESC LIMIT 1");
			
			front.Thread thread = null;
			while (rs.next()) {
				thread = new front.Thread((Integer) rs.getObject("threadID"),
					(Integer) rs.getObject("subForumID"),
					(String) rs.getObject("name"),
					(Integer) rs.getObject("createdBy"),
					rs.getTimestamp("createdOn"),
					(Integer) rs.getObject("views"),
					(Integer) rs.getObject("replies"));
			}
			be.disconnect();
			return thread;
		} catch (Exception e) {
			e.printStackTrace();
		}
		be.disconnect();
		return null;
	}
	
	public void incrementNumberOfThreads(int subForumID){
		InsertList insert = new InsertList();
		insert.addInt("numberOfThreads", "numberOfThreads+1");
		be.UpdateData("subforum", insert , new InsertList("subForumID","varchar",""+subForumID));
	}
	
	public void incrementReplies(int threadId){
		InsertList insert = new InsertList();
		insert.addInt("replies", "replies+1");
		be.UpdateData("thread", insert, new InsertList("threadID","varchar", ""+ threadId));
	}
	
	public void incrementViews(int threadId){
		InsertList insert = new InsertList();
		insert.addInt("views", "views+1");
		be.UpdateData("thread", insert, new InsertList("threadID","varchar","" + threadId));
	}
	
	public void incrementNumberOfPosts(int userID){
		InsertList insert = new InsertList();
		insert.addInt("numberOfPosts", "numberOfPosts+1");
		be.UpdateData("user", insert, new InsertList("userID","varchar","" + userID));
	}
	
	public String getNameOfThread(int threadID){
		try {
			InsertList where = new InsertList();
			where.addInt("threadID", ""+threadID);
			
			ResultSet rs = be.getData("thread", new InsertList("name"), where,"LIMIT 1");
			
			rs.next();
			String name = rs.getString("name");
			be.disconnect();
			return name;
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return "";
	}
	public ArrayList<Post> getAllPosts(int threadID){
		
		try {
			InsertList where = new InsertList();
			where.addInt("threadID", ""+threadID);
			ResultSet rs =  be.getData("post", null,where,"ORDER BY createdOn");
			
			ArrayList<Post> result = new ArrayList<Post>();
			while(rs.next()){
				result.add(new Post(
						rs.getInt("postID"),
						rs.getInt("threadID"),
						rs.getInt("userID"),
						rs.getString("content"),
						rs.getTimestamp("createdOn"),
						rs.getTimestamp("lasteditedOn")
						));
			}
			be.disconnect();
			return result;
		} catch (SQLException e) {
			System.out.println("SQL error!");
			e.printStackTrace();
		}catch (Exception e) {
			e.printStackTrace();
		}
		be.disconnect();
		return null;
	}
	
	public int getNumberOfViewsOnAThread(int threadID){
		try {
			InsertList where = new InsertList();
			where.addInt("threadID", ""+threadID);
			ResultSet rs = be.getData("thread", new InsertList("views"), where,"LIMIT 1");
			rs.next();
			int views = rs.getInt("views");
			be.disconnect();
			return views;
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return -1;
	}
	
	public int getNumberOfRepliesOnAThread(int threadID){
		try {
			InsertList where = new InsertList();
			where.addInt("threadID", ""+threadID);
			
			ResultSet rs = be.getData("thread", new InsertList("replies"), where,"LIMIT 1");
			
			rs.next();
			int replies=rs.getInt("replies");
			be.disconnect();
			return replies;
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return -1;
	}
	
	public void setNumberOfViesOnAThread(int threadID, int number){
		InsertList insert = new InsertList();
		insert.addInt("views", ""+number);
		be.UpdateData("thread", insert, new InsertList("threadID","varchar", ""+ threadID));
	}

	public void setNumberOfRepliesOnAThread(int threadID, int number){
		InsertList insert = new InsertList();
		insert.addInt("replies", ""+number);
		be.UpdateData("thread", insert, new InsertList("threadID","varchar","" + threadID));
	}
	
	public void setNumberOfThreadsOnASubForum(int subForumID, int number){
		InsertList insert = new InsertList();
		insert.addInt("numberOfThreads", ""+number);
		be.UpdateData("subforum", insert, new InsertList("subForumID","varchar","" + subForumID));
	}
	
	public Post getLatestPost(int threadID){
		try {
			InsertList where = new InsertList();
			where.addInt("threadID", ""+threadID);
			
			ResultSet rs = be.getData("post", null, where, "ORDER BY createdOn DESC LIMIT 1");
			
			Post post = null;
			
			while(rs.next()){
				post  = new Post(
					rs.getInt("postID"),
					rs.getInt("threadID"),
					rs.getInt("userID"),
					rs.getString("content"),
					rs.getTimestamp("createdOn"),
					rs.getTimestamp("lasteditedOn")
					);
			}
			be.disconnect();
			return post;
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		be.disconnect();
		return null;
	}
		
	
	public front.Thread getLatestThread(int subForumID){
		try {
			InsertList where = new InsertList();
			where.addInt("subForumID", ""+subForumID);
			ResultSet rs = be.getData("thread", null, where,"ORDER BY createdOn DESC LIMIT 1");
			
			front.Thread thread = null;
			
			while(rs.next()){
				thread  = new front.Thread(
						(Integer)rs.getObject("threadID"), 
						(Integer)rs.getObject("subForumID"), 
						(String)rs.getObject("name"), 
						(Integer)rs.getObject("createdBy"), 
						(Timestamp)rs.getObject("createdOn"), 
						(Integer)rs.getObject("views"), 
						(Integer)rs.getObject("replies"));
			}
			be.disconnect();
			return thread;
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		be.disconnect();
		return null;
	}
	
	public ArrayList<PM> getAllPMSToAUser(int userID){
		try {
			InsertList where = new InsertList();
			where.addInt("toUserID", ""+userID);
			
			ResultSet rs = be.getData("pm", null,where,"ORDER BY date DESC");
			ArrayList<PM> result = new ArrayList<PM>();
			PM row;
			while (rs.next()) {
				row = new PM((Integer) rs.getObject("pmID"),
						(Integer) rs.getObject("fromUserID"),
						(Integer) rs.getObject("toUserID"),
						(Timestamp) rs.getObject("date"),
						(String) rs.getObject("message"));
				result.add(row);
			}
			be.disconnect();
			return result;
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		be.disconnect();
		return null;
	}
	
	public ArrayList<PM> getAllPMSFromAUser(int userID){
		try {
			InsertList where = new InsertList();
			where.addInt("fromUserID", ""+userID);
			ResultSet rs = be.getData("pm", null,where,"ORDER BY date DESC");
			ArrayList<PM> result = new ArrayList<PM>();
			PM row;
			while (rs.next()) {
				row = new PM((Integer) rs.getObject("pmID"),
						(Integer) rs.getObject("fromUserID"),
						(Integer) rs.getObject("toUserID"),
						(Timestamp) rs.getObject("date"),
						(String) rs.getObject("message"));
				result.add(row);
			}
			be.disconnect();
			return result;
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		be.disconnect();
		return null;
	}
	
	
	public ArrayList<front.Thread> getAllThreadsByUser(int userID){
		try {
			InsertList where = new InsertList();
			where.addInt("createdBy", ""+userID);
			ResultSet rs =  be.getData("thread", null, where,"ORDER BY createdOn DESC");
			
			ArrayList<front.Thread> result = new ArrayList<front.Thread>();
			
			front.Thread row;
			while(rs.next()){
				row  = new front.Thread(
						(Integer)rs.getObject("threadID"), 
						(Integer)rs.getObject("subForumID"), 
						(String)rs.getObject("name"), 
						(Integer)rs.getObject("createdBy"), 
						(Timestamp)rs.getObject("createdOn"), 
						(Integer)rs.getObject("views"), 
						(Integer)rs.getObject("replies"));
				result.add(row);
			}
			System.out.println("Found " + result.size() + " threads!");
			be.disconnect();
			return result;
		} catch (SQLException e) {
			System.out.println("SQL error!");
			e.printStackTrace();
		}catch (Exception e) {
			e.printStackTrace();
		}
		be.disconnect();
		return null;
	}
	
	public ArrayList<Post> getAllPostsByUser(int userID){
		try {
			
			InsertList where = new InsertList();
			where.addInt("userID", ""+userID);
			
			ResultSet rs =  be.getData("thread", null,where, "ORDER BY createdOn DESC");
			
			ArrayList<Post> result = new ArrayList<Post>();
			
			while(rs.next()){
				result.add(new Post(
					(Integer)rs.getObject("postID"), 
					(Integer)rs.getObject("threadID"), 
					(Integer)rs.getObject("userID"), 
					(String)rs.getObject("content"), 
					(Timestamp)rs.getObject("createdOn"), 
					(Timestamp)rs.getObject("lasteditedOn")
					)
				);
			}
			System.out.println("Found " + result.size() + " post!");
			be.disconnect();
			return result;
		} catch (SQLException e) {
			System.out.println("SQL error!");
			e.printStackTrace();
		}catch (Exception e) {
			e.printStackTrace();
		}
		be.disconnect();
		return null;
	}

	public SubForum getSubforum(int subforumid) {
		try {
			InsertList where = new InsertList();
			where.addInt("subForumID", ""+subforumid);
			ResultSet rs = be.getData("subforum", null,where,"LIMIT 1");
			
			SubForum result = null;
			while(rs.next()){
				result  = new SubForum(
						(Integer)rs.getObject("subForumID"), 
						(String)rs.getObject("name"), 
						(Integer)rs.getObject("numberOfThreads"), 
						(Timestamp)rs.getObject("createdOn"),
						(Integer)rs.getObject("minimumRestrictionlvl"));
			}
			be.disconnect();
			return result;
		
		} catch (SQLException e) {
			System.out.println("PMgetEmail! catch!");
			e.printStackTrace();
		}
		be.disconnect();
		return null;
	}

	public User getUser(String username) {
		try {
			InsertList where = new InsertList();
			where.addVarchar("email", username);
			//where.addVarchar("password", password);
			ResultSet rs = be.getData("user", null,where,"LIMIT 1");
			
			User result = null;
			while(rs.next()){
				result  = new User(
					rs.getInt("userID"),
					rs.getInt("userlvl"),
					rs.getString("name"),
					rs.getString("email"),
					rs.getString("password"),
					rs.getTimestamp("joined"),
					rs.getInt("numberOfPosts")
				);
			}
			be.disconnect();
			return result;
		
		} catch (SQLException e) {
			System.out.println("PMgetEmail! catch!");
			e.printStackTrace();
		}
		be.disconnect();
		return null;
	}

	public front.Thread getThread(int threadID) {
		try {
			InsertList where = new InsertList();
			where.addInt("threadID", ""+threadID);
			ResultSet rs =  be.getData("thread", null, where,"LIMIT 1");
			
			
			front.Thread row = null;
			while(rs.next()){
				row  = new front.Thread(
					(Integer)rs.getObject("threadID"), 
					(Integer)rs.getObject("subForumID"), 
					(String)rs.getObject("name"), 
					(Integer)rs.getObject("createdBy"), 
					(Timestamp)rs.getObject("createdOn"), 
					(Integer)rs.getObject("views"), 
					(Integer)rs.getObject("replies"));
			}
			be.disconnect();
			return row;
		} catch (SQLException e) {
			System.out.println("SQL error!");
			e.printStackTrace();
		}catch (Exception e) {
			e.printStackTrace();
		}
		be.disconnect();
		return null;
	}

	public Post getPost(int postID) {
		try {
			InsertList where = new InsertList();
			where.addInt("postID", ""+postID);
			
			ResultSet rs = be.getData("post", null, where, "LIMIT 1");
			
			Post post = null;
			
			while(rs.next()){
				post  = new Post(
					rs.getInt("postID"),
					rs.getInt("threadID"),
					rs.getInt("userID"),
					rs.getString("content"),
					rs.getTimestamp("createdOn"),
					rs.getTimestamp("lasteditedOn")
				);
			}
			be.disconnect();
			return post;
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		be.disconnect();
		return null;
	}
}
